package com.gxuwz.medical.domain.house;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.gxuwz.medical.database.DbUtil;
import com.gxuwz.medical.domain.member.Member;
import com.gxuwz.medical.exception.UserNotFoundException;

/**
 * 家庭档案管理 户主基本信息
 * 
 * @author Administrator
 * 
 */
public class House {

	private String housemaster; // 户主名称
	private String familycode; // 家庭编号
	private String idcard; // 身份证（主键）
	private String sex; // 性别
	private String birthday; // 出生日期
	private String nation; // 民族
	private String health; // 健康状况
	private String culture; // 文化程度
	private String relationship; // 人员属性
	private String farmer; // 是否农业户口
	private String marriage; // 婚姻状况
	private String work; // 工作地址
	private String phone; // 联系电话
	private String housecode;

	public House(String housemaster, String familycode, String idcard,
			String sex, String birthday, String nation, String health,
			String culture, String relationship, String farmer,
			String marriage, String work, String phone, String housecode) {
		super();
		this.housemaster = housemaster;
		this.familycode = familycode;
		this.idcard = idcard;
		this.sex = sex;
		this.birthday = birthday;
		this.nation = nation;
		this.health = health;
		this.culture = culture;
		this.relationship = relationship;
		this.farmer = farmer;
		this.marriage = marriage;
		this.work = work;
		this.phone = phone;
		this.housecode = housecode;
	}

	public House(String familycode) throws UserNotFoundException {

		load(familycode);
	}

	public House() {
	}

	public void addHouse(String housemaster, String familycode, String idcard,
			String sex, String birthday, String nation, String health,
			String culture, String relationship, String farmer,
			String marriage, String work, String phone, String housecode)
			throws SQLException {
		Connection conn = null;
		this.housemaster = housemaster;
		this.familycode = familycode;
		this.idcard = idcard;
		this.sex = sex;
		this.birthday = birthday;
		this.nation = nation;
		this.health = health;
		this.culture = culture;
		this.relationship = relationship;
		this.farmer = farmer;
		this.marriage = marriage;
		this.work = work;
		this.phone = phone;
		this.housecode = housecode;
		try {
			conn = DbUtil.getConn();
			conn.setAutoCommit(false);
			saveToDB(conn);
			//与t_house表关联
			Member member = new Member();
			String nhzcode = housecode;
			String relation = "户主";
			member.saveAndMember(nhzcode,housemaster,familycode,idcard,
					sex,birthday,nation,health,
					culture,relation,farmer,work, phone, housecode);
			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			DbUtil.close(conn);
		}
	}

	protected void saveToDB(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sqlBuffer = new StringBuffer();
			sqlBuffer
					.append("insert into t_house(housemaster,familycode,idcard,sex,birthday,nation,health,culture,relationship,farmer,marriage,work,phone,housecode)");
			sqlBuffer.append("values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			String sql = sqlBuffer.toString();
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, this.housemaster);
			pstmt.setString(2, this.familycode);
			pstmt.setString(3, this.idcard);
			pstmt.setString(4, this.sex);
			pstmt.setString(5, this.birthday);
			pstmt.setString(6, this.nation);
			pstmt.setString(7, this.health);
			pstmt.setString(8, this.culture);
			pstmt.setString(9, this.relationship);
			pstmt.setString(10, this.farmer);
			pstmt.setString(11, this.marriage);
			pstmt.setString(12, this.work);
			pstmt.setString(13, this.phone);
			pstmt.setString(14, this.housecode);

			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("添加t_house时出现异常:",e);
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
		}
	}
	
	

	public void editHouse(String housemaster,String familycode, String housecode, String idcard,
			String sex, String birthday, String nation, String health,
			String culture, String relationship, String farmer,
			String marriage, String work, String phone) throws SQLException {
		Connection conn = null;
		this.housemaster = housemaster;
		this.familycode = familycode;
		this.housecode = housecode;
		this.idcard = idcard;
		this.sex = sex;
		this.birthday = birthday;
		this.nation = nation;
		this.health = health;
		this.culture = culture;
		this.relationship = relationship;
		this.farmer = farmer;
		this.marriage = marriage;
		this.work = work;
		this.phone = phone;
		try {
			conn = DbUtil.getConn();
			conn.setAutoCommit(false);
			editToDB(conn);
			Member member = new Member();
			member.editToMember(housemaster,familycode,
					sex,birthday,nation,health,
					culture,farmer,work, phone, housecode,idcard);
			conn.commit();
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			DbUtil.close(conn);
		}
	}
	

	private void editToDB(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sqlBuff = new StringBuffer();
			sqlBuff.append("update t_house set housemaster=?,housecode=?,sex=?,birthday=?,nation=?,health=?,culture=?,relationship=?,farmer=?,marriage=?,work=?,phone=? where idcard=?");
			pstmt = conn.prepareStatement(sqlBuff.toString());
			pstmt.setString(1, this.housemaster);
			pstmt.setString(2, this.housecode);
			pstmt.setString(3, this.sex);
			pstmt.setString(4, this.birthday);
			pstmt.setString(5, this.nation);
			pstmt.setString(6, this.health);
			pstmt.setString(7, this.culture);
			pstmt.setString(8, this.relationship);
			pstmt.setString(9, this.farmer);
			pstmt.setString(10, this.marriage);
			pstmt.setString(11, this.work);
			pstmt.setString(12, this.phone);
			pstmt.setString(13, this.idcard);

			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("修改t_house表时出错！",e);
		}
		DbUtil.close(pstmt);

	}

	public boolean CheckHouse(String familycode) throws Exception {
		try {
			Connection conn = DbUtil.getConn();
			StringBuffer sqlBuff = new StringBuffer();
			int index = 0;
			boolean flag = false;
			sqlBuff.append("select * from t_house where familycode=?");
			PreparedStatement pstmt = conn.prepareStatement(sqlBuff.toString());
			pstmt.setString(++index, familycode);
			ResultSet rs = pstmt.executeQuery();
			flag = rs.next();
			conn.close();
			// System.out.println("familycode:"+familycode);
			// System.out.println("flag:"+flag);
			return flag;
		} catch (Exception e) {
			throw new Exception("校验t_house的家庭编号时失败：");
		}
	}
	
	public boolean CheckIdcard(String idcard) throws SQLException {
		try {
			Connection conn = DbUtil.getConn();
			StringBuffer sqlBuff = new StringBuffer();
			int index = 0;
			boolean flag = false;
			sqlBuff.append("select * from t_house where idcard=?");
			PreparedStatement pstmt = conn.prepareStatement(sqlBuff.toString());
			pstmt.setString(++index, idcard);
			ResultSet rs = pstmt.executeQuery();
			flag = rs.next();
			conn.close();
			return flag;
		} catch (SQLException e) {
			throw new SQLException("校验t_house的身份证时失败：",e);
		}
	}

	private void load(String familycode) throws UserNotFoundException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DbUtil.getConn();
			pstmt = conn
					.prepareStatement("select * from t_house where familycode=?");
			int index = 1;
			pstmt.setString(index, familycode);
			rs = pstmt.executeQuery();
			if (rs != null && rs.next()) {
				this.familycode = rs.getString("familycode");
				this.housemaster = rs.getString("housemaster");
				this.idcard = rs.getString("idcard");
				this.sex = rs.getString("sex");
				this.birthday = rs.getString("birthday");
				this.nation = rs.getString("nation");
				this.health = rs.getString("health");
				this.culture = rs.getString("culture");
				this.relationship = rs.getString("relationship");
				this.farmer = rs.getString("farmer");
				this.marriage = rs.getString("marriage");
				this.work = rs.getString("work");
				this.phone = rs.getString("phone");
				this.housecode = rs.getString("housecode");
			}

		} catch (SQLException e) {
			throw new UserNotFoundException("Medical with id " + familycode
					+ " could not be loaded from the database.");
		} finally {
			DbUtil.close(rs, pstmt, conn);
		}
	}

	public static List<House> getNewsList(String where) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
//		System.out.println("where:" + where);

		List<House> newslist = new ArrayList<House>();

		conn = DbUtil.getConn();

		String sql = "select * from t_house where " + where;
		pstmt = conn.prepareStatement(sql);
//		System.out.println("sql:" + sql);
		ResultSet rs = pstmt.executeQuery(sql);
		try {
			while (rs.next()) {
				House news = new House();
				String housemaster = rs.getString("housemaster");
				String familycode = rs.getString("familycode");
				String idcard = rs.getString("idcard");
				String sex = rs.getString("sex");
				String birthday = rs.getString("birthday");
				String nation = rs.getString("nation");
				String health = rs.getString("health");
				String culture = rs.getString("culture");
				String relationship = rs.getString("relationship");
				String farmer = rs.getString("farmer");
				String marriage = rs.getString("marriage");
				String work = rs.getString("work");
				String phone = rs.getString("phone");
				String housecode = rs.getString("housecode");

				news.setHousemaster(housemaster);
				news.setFamilycode(familycode);
				news.setIdcard(idcard);
				news.setSex(sex);
				news.setBirthday(birthday);
				news.setNation(nation);
				news.setHealth(health);
				news.setCulture(culture);
				news.setRelationship(relationship);
				news.setFarmer(farmer);
				news.setMarriage(marriage);
				news.setWork(work);
				news.setPhone(phone);
				news.setHousecode(housecode);

				newslist.add(news);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return newslist;
	}
	
	public void delhz(String familycode) throws SQLException {
		this.familycode = familycode;
		Connection conn = null;
		try{
			conn = DbUtil.getConn();
			conn.setAutoCommit(false);
			delForDB(conn);
			unbindMember(conn);
			conn.commit();
		}catch(SQLException e){
			conn.rollback();
			throw e;
		}finally{
			DbUtil.close(conn);
		}
	}
	
	private void delForDB(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sqlBuff = new StringBuffer();
			sqlBuff.append("delete from t_member where familycode=?");
			pstmt = conn.prepareStatement(sqlBuff.toString());
			pstmt.setString(1, familycode);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("删除与t_family关联的t_member表时出错！",e);
		} finally {
			DbUtil.close(pstmt);
		}
	}
	
	
	private void unbindMember(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sqlBuff = new StringBuffer();
			sqlBuff.append("delete from t_member where familycode=?");
			pstmt = conn.prepareStatement(sqlBuff.toString());
			pstmt.setString(1, familycode);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("删除与t_family关联的t_member表时出错！",e);
		} finally {
			DbUtil.close(pstmt);
		}
	}


	public String getHousemaster() {
		return housemaster;
	}

	public void setHousemaster(String housemaster) {
		this.housemaster = housemaster;
	}

	public String getFamilycode() {
		return familycode;
	}

	public void setFamilycode(String familycode) {
		this.familycode = familycode;
	}

	public String getIdcard() {
		return idcard;
	}

	public void setIdcard(String idcard) {
		this.idcard = idcard;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getBirthday() {
		return birthday;
	}

	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}

	public String getNation() {
		return nation;
	}

	public void setNation(String nation) {
		this.nation = nation;
	}

	public String getHealth() {
		return health;
	}

	public void setHealth(String health) {
		this.health = health;
	}

	public String getCulture() {
		return culture;
	}

	public void setCulture(String culture) {
		this.culture = culture;
	}

	public String getRelationship() {
		return relationship;
	}

	public void setRelationship(String relationship) {
		this.relationship = relationship;
	}

	public String getFarmer() {
		return farmer;
	}

	public void setFarmer(String farmer) {
		this.farmer = farmer;
	}

	public String getMarriage() {
		return marriage;
	}

	public void setMarriage(String marriage) {
		this.marriage = marriage;
	}

	public String getWork() {
		return work;
	}

	public void setWork(String work) {
		this.work = work;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getHousecode() {
		return housecode;
	}

	public void setHousecode(String housecode) {
		this.housecode = housecode;
	}

}
